package com.tdk.bts.dao;


import com.tdk.bts.constants.Constants;
import com.tdk.bts.entity.BookInfo;
import com.tdk.bts.service.BookInfoService;
import com.tdk.bts.util.DruidConnectionUtil;
import com.tdk.bts.vo.BookInfoVO;
import com.tdk.bts.vo.BookVO;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class BookInfoServiceDao {
    private static Connection conn = null;
    private static JdbcTemplate jdbcTemplate = null;

    static {
        if (conn == null) {
            try {
                conn = DruidConnectionUtil.getConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (jdbcTemplate==null){
            jdbcTemplate = DruidConnectionUtil.getJDBCTemplate();
        }
    }
    //分页查询预订信息
    public List<BookVO> findBookInfo(int pageNo) throws SQLException {
        List<BookVO> list = new ArrayList<>();
        BookVO bookVO = null;
        int m = (pageNo -1)* Constants.PAGE_SIZE;
        String sql = "select t_film.name as filmName," +
                "t_film_screen.start_day as startDay," +
                "t_film_screen.start_time as startTime," +
                "t_theater.name as theater_name," +
                "t_book_info.seat_x as seatX," +
                "t_book_info.seat_y as seatY," +
                "t_book_info.status as status\n" +
                "FROM t_film_screen\n" +
                "INNER JOIN t_film \n" +
                "on t_film_screen.film_id = t_film.id\n" +
                "INNER JOIN t_book_info\n" +
                "on t_film_screen.id = t_book_info.screen_id\n" +
                "INNER JOIN t_theater\n" +
                "on t_film_screen.theater_id = t_theater.id limit ?,?";
        PreparedStatement preSt = conn.prepareStatement(sql);
        preSt.setInt(1,m);
        preSt.setInt(2,Constants.PAGE_SIZE);
        ResultSet rs = preSt.executeQuery();
        while (rs.next()){
            bookVO = new BookVO();
            //从数据库获取对应列名的值
            String filmName = rs.getString("filmName");
            bookVO.setFilmName(filmName);
            Date start_day = rs.getDate("startDay");
            bookVO.setStartDay(start_day);
            String time = rs.getTime("startTime").toString();
            bookVO.setTime(time);
            String theaterName = rs.getString("theater_name");
            bookVO.setTheaterName(theaterName);
            int seat_x = rs.getInt("seatX");
            bookVO.setSeatX(seat_x);
            int seat_y = rs.getInt("seatY");
            bookVO.setSeatY(seat_y);
            int status = rs.getInt("status");
            bookVO.setStatus(status);
            list.add(bookVO);
        }
        DruidConnectionUtil.closeAll(preSt,rs);
        return list;
    }

    //获取所有预订信息
    public int BookInfoCount() throws SQLException{
        String sql = "select COUNT(id) from t_Book_info";
        PreparedStatement preSt = conn.prepareStatement(sql);
        ResultSet rs = preSt.executeQuery();
        int num = 0;
        if(rs.next()){
            num = rs.getInt(1);
        }
        DruidConnectionUtil.closeAll(preSt,rs);
        return num;
    }

    //获取所有取票信息
    public BookInfoVO getTicketInfo(String phone,String validCode) throws SQLException{
        String sql = "select film.name , screen.start_day , screen.start_time , theater.name , bookInfo.seat_x,bookInfo.seat_y\n" +
                "from t_book_info as bookInfo , t_film_screen as screen , t_theater as theater , t_film as film\n" +
                "where bookInfo.screen_id = screen.id " +
                "AND screen.film_id = film.id " +
                "AND screen.theater_id = theater.id\n" +
                "AND phone = ? " +
                "AND validCode = ?";
        PreparedStatement preSt = conn.prepareStatement(sql);
        preSt.setString(1,phone);
        preSt.setString(2,validCode);
        ResultSet rs = preSt.executeQuery();
        BookInfoVO bookInfoVO = null;
        while (rs.next()){
            bookInfoVO = new BookInfoVO();
            String filmName = rs.getString("film.name");
            bookInfoVO.setFilmName(filmName);
            String startDay = rs.getString("screen.start_day");
            bookInfoVO.setStartDay(startDay);
            String startTime = rs.getString("screen.start_time");
            bookInfoVO.setStartTime(startTime);
            String theaterName = rs.getString("theater.name");
            bookInfoVO.setTheaterName(theaterName);
            List<BookInfo> bookInfo = getBookInfo(phone, validCode);
            bookInfoVO.setBookInfos(bookInfo);
        }
        return bookInfoVO;
    }

    //取票操作
    public void receiveTicket(String phone, String validCode){
        String sql="UPDATE t_book_info SET status=1 WHERE phone=? and validCode=?";
        jdbcTemplate.update(sql,phone,validCode);
    }

    private static List<BookInfo> getBookInfo(String phone,String validCode) {
        String sql  ="select * from t_book_info where phone=? AND validCode=?";
        List<BookInfo> list = jdbcTemplate.query(sql, new RowMapper<BookInfo>() {
            @Override
            public BookInfo mapRow(ResultSet resultSet, int i) throws SQLException {
                BookInfo bookInfo = new BookInfo();
                int id = resultSet.getInt("id");
                bookInfo.setId(id);
                int screenId = resultSet.getInt("screen_id");
                bookInfo.setScreenId(screenId);
                int seatX = resultSet.getInt("seat_x");
                bookInfo.setSeatX(seatX);
                int seatY = resultSet.getInt("seat_y");
                bookInfo.setSeatY(seatY);
                String phone = resultSet.getString("phone");
                bookInfo.setPhone(phone);
                String validCode = resultSet.getString("validCode");
                bookInfo.setValidCode(validCode);
                int status = resultSet.getInt("status");
                bookInfo.setStatus(status);
                return bookInfo;
            }
        },phone ,validCode);
        return list;
    }

    public List<BookInfo> findBookInfoByScreenIds(int id) throws SQLException {
        List<BookInfo> list = new ArrayList<>();
        String sql = "select bookInfo.id , bookInfo.screen_id , bookInfo.seat_x ,bookInfo.seat_y , " +
                "bookInfo.phone , bookInfo.validCode , bookInfo.status " +
                "from t_book_info bookInfo  " +
                "where  bookInfo.screen_id = ?";
        PreparedStatement preSt = conn.prepareStatement(sql);
        preSt.setInt(1,id);
        ResultSet rs = preSt.executeQuery();
        BookInfo bookInfo = null;
        while (rs.next()){
            bookInfo = new BookInfo();
            Integer bookInfoId = rs.getInt("bookInfo.id");
            bookInfo.setId(bookInfoId);
            Integer screenId = rs.getInt("bookInfo.screen_id");
            bookInfo.setScreenId(screenId);
            Integer seatX = rs.getInt("bookInfo.seat_x");
            bookInfo.setSeatX(seatX);
            Integer seatY = rs.getInt("bookInfo.seat_y");
            bookInfo.setSeatY(seatY);
            String phone = rs.getString("bookInfo.phone");
            bookInfo.setPhone(phone);
            String validCode = rs.getString("bookInfo.validCode");
            bookInfo.setValidCode(validCode);
            Integer status = rs.getInt("bookInfo.status");
            bookInfo.setStatus(status);
            list.add(bookInfo);

        }
        DruidConnectionUtil.closeAll(preSt,rs);
        return list;
    }

    public void insertValidCodes(int screenId, String[] seats, String phone,String validC) throws SQLException {
        String sql = "INSERT INTO t_book_info(screen_id,seat_x,seat_y,phone,validCode,status) " +
                "VALUES(?,?,?,?,?,0)";
        PreparedStatement preSt = conn.prepareStatement(sql);
        for(int i = 0; i < seats.length/2; i++){
            String x = seats[2*i].substring(1,2);
            String y = seats[2*i+1].substring(0,1);

            preSt.setInt(1,screenId);
            preSt.setInt(2,Integer.parseInt(x));
            preSt.setInt(3,Integer.parseInt(y));
            preSt.setString(4,phone);
            preSt.setString(5,validC);
            preSt.execute();
        }
        DruidConnectionUtil.closeAll(preSt,null);
    }

}
